{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Download and manipulate data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook contains information on downloading the Quandl Wiki stock prices and a few other sources that we use throughout the book. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports & Settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pandas_datareader.data as web\n",
    "\n",
    "pd.set_option('display.expand_frame_repr', False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set Data Store path"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Modify path if you would like to store the data elsewhere and change the notebooks accordingly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "DATA_STORE = Path('assets.h5')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Quandl Wiki Prices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Quandl](https://www.quandl.com/) makes available a [dataset](https://www.quandl.com/databases/WIKIP/documentation) with stock prices, dividends and splits for 3000 US publicly-traded companies. Quandl decided to discontinue support in favor of its commercial offerings but the historical data are still useful to demonstrate the application of the machine learning solutions in the book, just ensure you implement your own algorithms on current data.\n",
    "\n",
    "> As of April 11, 2018 this data feed is no longer actively supported by the Quandl community. We will continue to host this data feed on Quandl, but we do not recommend using it for investment or analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Follow the instructions to create a free [Quandl]([Quandl](https://www.quandl.com/)) account\n",
    "2. [Download](https://www.quandl.com/databases/WIKIP/usage/export) the entire WIKI/PRICES data\n",
    "3. Extract the .zip file,\n",
    "4. Move to this directory and rename to wiki_prices.csv\n",
    "5. Run the below code to store in fast HDF format (see [Chapter 02 on Market & Fundamental Data](../02_market_and_fundamental_data) for details)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = (pd.read_csv('wiki_prices.csv',\n",
    "                 parse_dates=['date'],\n",
    "                 index_col=['date', 'ticker'],\n",
    "                 infer_datetime_format=True)\n",
    "     .sort_index())\n",
    "\n",
    "print(df.info(null_counts=True))\n",
    "with pd.HDFStore(DATA_STORE) as store:\n",
    "    store.put('quandl/wiki/prices', df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Wiki Prices Metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Follow the instructions to create a free [Quandl]([Quandl](https://www.quandl.com/)) account if you haven't done so yet\n",
    "2. Find link to download wiki metadata under Companies](https://www.quandl.com/databases/WIKIP/documentation) or use the download link with your API_KEY: https://www.quandl.com/api/v3/databases/WIKI/metadata?api_key=<API_KEY>\n",
    "3. Extract the .zip file,\n",
    "4. Move to this directory and rename to wiki_stocks.csv\n",
    "5. Run the following code to store in fast HDF format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('wiki_stocks.csv')\n",
    "df = pd.concat([df.loc[:, 'code'].str.strip(),\n",
    "                df.loc[:, 'name'].str.split('(', expand=True)[0].str.strip().to_frame('name')], axis=1)\n",
    "\n",
    "print(df.info(null_counts=True))\n",
    "with pd.HDFStore(DATA_STORE) as store:\n",
    "    store.put('quandl/wiki/stocks', df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## S&P 500 Prices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following code downloads historical S&P 500 prices from FRED (only last 10 years of daily data is freely available)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = web.DataReader(name='SP500', data_source='fred', start=2008)\n",
    "print(df.info())\n",
    "with pd.HDFStore(DATA_STORE) as store:\n",
    "    store.put('sp500/prices', df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### S&P 500 Constituents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following code downloads the current S&P 500 constituents from [Wikipedia](https://en.wikipedia.org/wiki/List_of_S%26P_500_companies)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 505 entries, MMM to ZTS\n",
      "Data columns (total 7 columns):\n",
      "name                 505 non-null object\n",
      "gics_sector          505 non-null object\n",
      "gics_sub_industry    505 non-null object\n",
      "location             505 non-null object\n",
      "first_added          402 non-null object\n",
      "cik                  505 non-null int64\n",
      "founded              172 non-null object\n",
      "dtypes: int64(1), object(6)\n",
      "memory usage: 31.6+ KB\n",
      "None\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/stefan/.pyenv/versions/miniconda3-latest/envs/ml4t/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3214: PerformanceWarning: \n",
      "your performance may suffer as PyTables will pickle object types that it cannot\n",
      "map directly to c-types [inferred_type->mixed,key->block0_values] [items->['name', 'gics_sector', 'gics_sub_industry', 'location', 'first_added', 'founded']]\n",
      "\n",
      "  if (yield from self.run_code(code, result)):\n"
     ]
    }
   ],
   "source": [
    "url = 'https://en.wikipedia.org/wiki/List_of_S%26P_500_companies'\n",
    "df = pd.read_html(url, header=0)[0]\n",
    "df.columns = ['name', 'ticker', 'sec_filings', 'gics_sector', 'gics_sub_industry',\n",
    "              'location', 'first_added', 'cik', 'founded']\n",
    "df = df.drop('sec_filings', axis=1).set_index('ticker')\n",
    "print(df.info())\n",
    "with pd.HDFStore(DATA_STORE) as store:\n",
    "    store.put('sp500/stocks', df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Metadata on US-traded companies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following downloads several attributes for [companies](https://www.nasdaq.com/screening/companies-by-name.aspx) traded on NASDAQ, AMEX and NYSE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 6852 entries, YI to ZYME\n",
      "Data columns (total 6 columns):\n",
      "name         6852 non-null object\n",
      "lastsale     6742 non-null float64\n",
      "marketcap    5835 non-null object\n",
      "ipoyear      3113 non-null float64\n",
      "sector       5292 non-null object\n",
      "industry     5292 non-null object\n",
      "dtypes: float64(2), object(4)\n",
      "memory usage: 374.7+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "url = 'https://www.nasdaq.com/screening/companies-by-name.aspx?letter=0&exchange={}&render=download'\n",
    "exchanges = ['NASDAQ', 'AMEX', 'NYSE']\n",
    "df = pd.concat([pd.read_csv(url.format(ex)) for ex in exchanges]).dropna(how='all', axis=1)\n",
    "df = df.rename(columns=str.lower).set_index('symbol').drop('summary quote', axis=1)\n",
    "df = df[~df.index.duplicated()]\n",
    "print(df.info()) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>lastsale</th>\n",
       "      <th>marketcap</th>\n",
       "      <th>ipoyear</th>\n",
       "      <th>sector</th>\n",
       "      <th>industry</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>symbol</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>YI</th>\n",
       "      <td>111, Inc.</td>\n",
       "      <td>7.7000</td>\n",
       "      <td>$627.89M</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>Medical/Nursing Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PIH</th>\n",
       "      <td>1347 Property Insurance Holdings, Inc.</td>\n",
       "      <td>5.1700</td>\n",
       "      <td>$31.09M</td>\n",
       "      <td>2014.0</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Property-Casualty Insurers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PIHPP</th>\n",
       "      <td>1347 Property Insurance Holdings, Inc.</td>\n",
       "      <td>24.7628</td>\n",
       "      <td>$17.33M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Property-Casualty Insurers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TURN</th>\n",
       "      <td>180 Degree Capital Corp.</td>\n",
       "      <td>1.8922</td>\n",
       "      <td>$58.89M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Finance/Investors Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FLWS</th>\n",
       "      <td>1-800 FLOWERS.COM, Inc.</td>\n",
       "      <td>18.8200</td>\n",
       "      <td>$1.21B</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>Consumer Services</td>\n",
       "      <td>Other Specialty Stores</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          name  lastsale marketcap  ipoyear             sector                    industry\n",
       "symbol                                                                                                                    \n",
       "YI                                   111, Inc.    7.7000  $627.89M   2018.0        Health Care    Medical/Nursing Services\n",
       "PIH     1347 Property Insurance Holdings, Inc.    5.1700   $31.09M   2014.0            Finance  Property-Casualty Insurers\n",
       "PIHPP   1347 Property Insurance Holdings, Inc.   24.7628   $17.33M      NaN            Finance  Property-Casualty Insurers\n",
       "TURN                  180 Degree Capital Corp.    1.8922   $58.89M      NaN            Finance  Finance/Investors Services\n",
       "FLWS                   1-800 FLOWERS.COM, Inc.   18.8200    $1.21B   1999.0  Consumer Services      Other Specialty Stores"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert market cap information to numerical format"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Market cap is provided as strings so we need to convert it to numerical format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "M    3388\n",
       "B    2441\n",
       "0       6\n",
       "Name: suffix, dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mcap = df[['marketcap']].dropna()\n",
    "mcap['suffix'] = mcap.marketcap.str[-1]\n",
    "mcap.suffix.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Keep only values with value units:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 5829 entries, YI to ZYME\n",
      "Data columns (total 2 columns):\n",
      "marketcap    5829 non-null float64\n",
      "suffix       5829 non-null object\n",
      "dtypes: float64(1), object(1)\n",
      "memory usage: 296.6+ KB\n"
     ]
    }
   ],
   "source": [
    "mcap = mcap[mcap.suffix.str.endswith(('B', 'M'))]\n",
    "mcap.marketcap = pd.to_numeric(mcap.marketcap.str[1:-1])\n",
    "mcaps = {'M': 1e6, 'B': 1e9}\n",
    "for symbol, factor in mcaps.items():\n",
    "    mcap.loc[mcap.suffix == symbol, 'marketcap'] *= factor\n",
    "mcap.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count              5,829\n",
       "mean       7,495,050,037\n",
       "std       36,240,773,357\n",
       "min            1,410,000\n",
       "10%           35,188,000\n",
       "20%           92,888,000\n",
       "30%          196,769,999\n",
       "40%          345,400,000\n",
       "50%          619,210,000\n",
       "60%        1,117,999,999\n",
       "70%        2,200,000,000\n",
       "80%        4,724,000,000\n",
       "90%       13,713,999,999\n",
       "max      961,260,000,000\n",
       "Name: marketcap, dtype: object"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['marketcap'] = mcap.marketcap\n",
    "df.marketcap.describe(percentiles=np.arange(.1, 1, .1).round(1)).apply(lambda x: f'{int(x):,d}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Store result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/stefan/.pyenv/versions/miniconda3-latest/envs/ml4t/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3214: PerformanceWarning: \n",
      "your performance may suffer as PyTables will pickle object types that it cannot\n",
      "map directly to c-types [inferred_type->mixed,key->block1_values] [items->['name', 'sector', 'industry']]\n",
      "\n",
      "  if (yield from self.run_code(code, result)):\n"
     ]
    }
   ],
   "source": [
    "with pd.HDFStore(DATA_STORE) as store:\n",
    "    store.put('us_equities/stocks', df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bond Price Indexes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following code downloads several bond indexes from the Federal Reserve Economic Data service ([FRED](https://fred.stlouisfed.org/))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "securities = {'BAMLCC0A0CMTRIV'   : 'US Corp Master TRI',\n",
    "              'BAMLHYH0A0HYM2TRIV': 'US High Yield TRI',\n",
    "              'BAMLEMCBPITRIV'    : 'Emerging Markets Corporate Plus TRI',\n",
    "              'GOLDAMGBD228NLBM'  : 'Gold (London, USD)',\n",
    "              'DGS10'             : '10-Year Treasury CMR',\n",
    "              }\n",
    "\n",
    "df = web.DataReader(name=list(securities.keys()), data_source='fred', start=2000)\n",
    "df = df.rename(columns=securities).dropna(how='all').resample('B').mean()\n",
    "\n",
    "with pd.HDFStore(DATA_STORE) as store:\n",
    "    store.put('fred/assets', df)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
